SQL 逆引き

「table設計が悪い」ということもありうる

アンチパターンであることは認識しつつも、それを解決する方法を探る

この辺も添えておきたい

RDBMS名

table図の雑な図

SQL例

同じkeyを持つものを足し合わせた結果を得る

https://gyazo.com/ab8f17c77ad6dd47907dcad9968a209b

code:sql

SELECT

id,

SUM(price) OVER (PARTITION BY origin_id) as total

FROM orders;

mysql v8

WINDOW関数を用いないとめんどい感じになる

だから、inner joinで自己結合しないといけない

code:sql

SELECT o1.id, o.total

FROM orders AS o1

INNER JOIN

( SELECT o2.id, SUM(o2.price) AS total

FROM orders AS o2

GROUP BY os.key

) as o ON o.origin_id = o1.origin_id

住所の履歴を辿る

中間テーブル的

こういうのを作れば、欲しい物が得られる、というのがわかる

起点と、それ以外の部分のtableを作って、UNIONして作る

良い例題

code:SQL

SELECT

prefecture,

CASE WHEN sex = 1 THEN pop ELSE 0 END AS pop_men,

CASE WHEN sex = 2 THEN pop ELSE 0 END AS pop_wom

FROM Population

後にsumするので0以外ではいけない

code:sql

SELECT

emp_name,

CASE WHEN COUNT(*) = 1 THEN MAX(team)

WHEN COUNT(*) = 2 THEN "2つを兼務"

ELSE "3つ以上を兼務"

END

AS team

FROM Employees

GROUP BY emp_name;

間違えたところ

GROUP BY emt_idと書いていた

最初のWHEN節でMAX関数を使う必要があるところ

ムズない？

どういう思考回路？

GROUP BYあるから関数必要

→恒等関数だが

→ないのでMAX

みたいな？

数値とNULLが混在するカラムでORDER BY ASCするときに

1,2,3 NULL, NULLというように並べたい

code:sql

select *

FROM product AS p

INNER JOIN product_tag AS pt

ON pt.product_id = p.id

WHERE pt.tag_id = 1

ORDER BY

CASE WHEN pt.sort_no IS NULL THEN 1 ELSE 0 END,

pt.sort_no

window関数の例をいくつかみたい

parent_idとchild_idというcolumnを持っているときに、どちらに入っているかを特定したい

仕様

columnにparent_idとchild_idがある

これは例えば入れ子のあるカテゴリなどを想定している

table:例

id parent_id child_id

1 2

1 3

1 4

5 6

5 7

8 9

ここで、何かしらのidを一つ与えた時にそのidが

parent_idなのか

child_idなのか

どちらにも入っていないのか

を一発で特定するSQLを書きたい

DBから「ある特定のユーザーを消す」などをしたい時に

こういう場合に、userを基点として、どのtableと依存関係があるのか

もっというと、どういうSQLを書いたらそのuserをDB全体から完全に消せるのか

特に、user1人ではなく、条件を満たす全てのuserに対して行いたいときなど

あるuserは2つのtableと依存関係があるが

別のuserは4つのtableと依存関係がある

ということもある

これの一番大きい集合を特定したい